package DAO;

// <editor-fold defaultstate="collapsed" desc="HeaderFiles">
import Exception.KwickKioskDBException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.util.*;
import DTO.Clerk;
// </editor-fold>

/**
 * This class represents abstraction for data access from clerk table in database.
 * This class is called from DAO as a proxy.
 * @see DAO
 * @Date Mar 30, 2011
 * @author Tony Lei
 */
public class ClerkDAO {

    private Connection conn;
    private static final Logger logger = Logger.getLogger(ClerkDAO.class.getName());

    /**
     * constructor for this class.
     * @param conn connection object
     */
    public ClerkDAO(Connection conn) {
        this.conn = conn;
    }

    /**
     * This method inserts clerk information into database.
     * @param passCode a string representing clerk password
     * @param firstName a string representing clerk first name
     * @param lastName a string representing clerk last name
     * @return an integer representing clerk id.
     * @throws {@link KwickKioskDBException}
     */
    public int registerClerk(String passCode, String firstName, String lastName)
            throws KwickKioskDBException {

        Statement sqlStatement;
        try {
            sqlStatement = conn.createStatement();
            String sqlText = "INSERT INTO clerk "
                    + "(passcode, firstName, lastName) VALUES ("
                    + "'" + passCode + "', "
                    + "'" + firstName + "', "
                    + "'" + lastName + "')";
            if (updateDB(sqlText)) {
                sqlText = "select max(c.ckid) from clerk c";
                ResultSet rs = sqlStatement.executeQuery(sqlText);
                if (rs.next()) {
                    int newClerkID = rs.getInt(1);
                    rs.close();
                    sqlStatement.close();
                    return newClerkID;
                } else {
                    rs.close();
                    sqlStatement.close();
                    return -1;
                }
            }
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, null, "ClerkDAO : registerClerk" + ex);
        }
        return -1;
    }

    /**
     * This method updates clerk information into database.
     * @param c clerk DTO object encapsulating clerk information
     * @return a boolean flag 
     * @throws {@link KwickKioskDBException}
     */
    public boolean updateClerk(Clerk c) throws KwickKioskDBException {
        String sqlText = "update clerk set "
                + "passcode='" + c.getPassCode() + "', "
                + "firstName='" + c.getFirstName() + "', "
                + "lastName='" + c.getLastName() + "' "
                + "where ckid=" + c.getID();
        return updateDB(sqlText);
    }

    /**
     * This method deletes a clerk tuple from database
     * @param ckid an integer representing clerk id
     * @return a boolean flag
     * @throws {@link KwickKioskDBException}
     */
    public boolean removeClerk(int ckid) throws KwickKioskDBException {
        String sqlText = "DELETE FROM clerk WHERE ckid=" + ckid;
        return updateDB(sqlText);
    }

    /**
     * This method retrieves clerk information from database
     * @param ckid an integer
     * @return ClerkDTO object encapsulating clerk information
     * @throws {@link KwickKioskDBException}
     */
    public Clerk searchByID(int ckid) throws KwickKioskDBException {
        Statement sqlStatement;
        try {
            sqlStatement = conn.createStatement();
            String sqlText = "select * "
                    + "from clerk c "
                    + "where c.ckid=" + ckid;
            ResultSet rs = sqlStatement.executeQuery(sqlText);
            if (rs.next()) {
                Clerk theClerk = new Clerk(rs.getInt(1),
                        rs.getString(2),
                        rs.getString(3),
                        rs.getString(4));
                rs.close();
                sqlStatement.close();
                return theClerk;
            }
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, null, "ClerkDAO : searchByID" + ex);
            throw new KwickKioskDBException("ClerkDAO : searchByID",
                    ex.getMessage());
        }
        return null;
    }

    /**
     * This method retrieves clerk information from database by a first name criteria
     * @param name a string
     * @return an array list object
     * @throws {@link KwickKioskDBException}
     */
    public ArrayList<Clerk> searchByFirstName(String name)
            throws KwickKioskDBException {
        Statement sqlStatement;
        ArrayList<Clerk> list = new ArrayList<Clerk>();
        try {
            sqlStatement = conn.createStatement();
            String sqlText = "select * "
                    + "from clerk c "
                    + "where c.firstName like '%" + name + "%'";
            ResultSet rs = sqlStatement.executeQuery(sqlText);
            while (rs.next()) {
                list.add(new Clerk(rs.getInt(1),
                        rs.getString(2),
                        rs.getString(3),
                        rs.getString(4)));
            }
            rs.close();
            sqlStatement.close();
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, null, "ClerkDAO : searchByFirstName" + ex);
            throw new KwickKioskDBException("ClerkDAO : searchByFirstName",
                    ex.getMessage());
        }
        return list;
    }

    /**
     * This method retrieves clerk information by last name criteria
     * @param name string
     * @return an array list object
     * @throws {@link KwickKioskDBException}
     */
    public ArrayList<Clerk> searchByLastName(String name)
            throws KwickKioskDBException {

        Statement sqlStatement;
        ArrayList<Clerk> list = new ArrayList<Clerk>();
        try {
            sqlStatement = conn.createStatement();
            String sqlText = "select * "
                    + "from clerk c "
                    + "where c.lastName like '%" + name + "%'";
            ResultSet rs = sqlStatement.executeQuery(sqlText);
            while (rs.next()) {
                list.add(new Clerk(rs.getInt(1),
                        rs.getString(2),
                        rs.getString(3),
                        rs.getString(4)));
            }
            rs.close();
            sqlStatement.close();
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, null, "ClerkDAO : searchByLastName" + ex);
            throw new KwickKioskDBException("ClerkDAO : searchByLastName",
                    ex.getMessage());
        }
        return list;
    }

    /**
     * This method retrieves information of all clerks in database
     * @return an array list object
     * @throws {@link KwickKioskDBException}
     */
    public ArrayList<Clerk> getAllClerks() throws KwickKioskDBException {
        Statement sqlStatement;
        ArrayList<Clerk> list = new ArrayList<Clerk>();
        try {
            sqlStatement = conn.createStatement();
            String sqlText = "select * "
                    + "from clerk c";
            ResultSet rs = sqlStatement.executeQuery(sqlText);
            while (rs.next()) {
                list.add(new Clerk(rs.getInt(1),
                        rs.getString(2),
                        rs.getString(3),
                        rs.getString(4)));
            }
            rs.close();
            sqlStatement.close();
        } catch (SQLException ex) {
            logger.log(Level.SEVERE, null, "ClerkDAO : getALLClerks" + ex);
            throw new KwickKioskDBException("ClerkDAO : getAllCler",
                    ex.getMessage());
        }
        return list;
    }

    /**
     * This method updates database using data manipulation query
     * @param sqlText string for query
     * @return a boolean flag
     * @throws {@link KwickKioskDBException}
     */
    private boolean updateDB(String sqlText) throws KwickKioskDBException {
        Statement sqlStatement;
        try {
            sqlStatement = conn.createStatement();
            int value = sqlStatement.executeUpdate(sqlText);
            conn.commit();
            sqlStatement.close();
            if (value == 1) {
                return true;
            }
        } catch (SQLException ex) {
            try {
                conn.rollback();
                logger.log(Level.SEVERE, null, "ClerkDAO : updateDB" + ex);
                throw new KwickKioskDBException("ClerkDAO : updateDB",
                        ex.getMessage());
            } catch (SQLException ex2) {
                logger.log(Level.SEVERE, null, "ClerkDAO : updateDB" + ex2);
                throw new KwickKioskDBException("ClerkDAO : updateDB",
                        ex2.getMessage());
            }
        }

        return false;
    }
}
